package weaver.interfaces.qz.job;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import weaver.conn.RecordSet;
import weaver.general.BaseBean;
import weaver.general.TimeUtil;
import weaver.interfaces.schedule.BaseCronJob;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

public class RosterDataCornJob extends BaseCronJob {
    private BaseBean bb = new BaseBean();
    private String date;

    @Override
    public void execute() {
        htxy();
        zlxx();
        jjlxrxx();
        rzlc();
        age();
        //干部
        cadre();
    }

    void cadre() {
        try {
            RecordSet rs = new RecordSet();
            String sql = "SELECT t.yg, t.sfgb,t6.selectname as sfgbtxt, t.gbly,t5.selectname as gblytxt, t.qcccxjggsjzw, t.dyzysq, t.zymb, \n" +
                    "       t.grqkbc, t.wdd,t4.selectname as wddtxt, t.jrzxm,h2.lastname as jrzxmtxt, t.jrzcsd, t3.selectname as jrzcsdtxt,t.ydyyd, t2.selectname as ydyydtxt,t.djls,e.id as exist,h.lastname as djlstxt\n" +
                    "\t   \n" +
                    "FROM uf_gbgl t\n" +
                    "INNER JOIN (\n" +
                    "    SELECT yg, MAX(modedatacreatedate) AS max_date\n" +
                    "    FROM uf_gbgl\n" +
                    "    GROUP BY yg\n" +
                    ") latest ON t.yg = latest.yg AND t.modedatacreatedate = latest.max_date\n" +
                    "left join uf_cadre e on e.yg=t.yg\n" +
                    "left join hrmresource h on h.id=t.djls\n" +
                    "left join workflow_SelectItem t2 on t2.fieldid=19513 and t2.selectvalue=t.ydyyd\n" +
                    "left join workflow_SelectItem t3 on t3.fieldid=19511 and t3.selectvalue=t.jrzcsd\n" +
                    "left join workflow_SelectItem t4 on t4.fieldid=19513 and t4.selectvalue=t.wdd\n" +
                    "left join workflow_SelectItem t5 on t5.fieldid=19515 and t5.selectvalue=t.gbly\n" +
                    "left join workflow_SelectItem t6 on t6.fieldid=19508 and t6.selectvalue=t.sfgb\n" +
                    "left join hrmresource h2 on h2.id=t.jrzxm\n" +
                    "where t.modedatacreatedate>='" + TimeUtil.dateAdd(TimeUtil.getCurrentDateString(), -1) + "'\n" +
                    "ORDER BY t.modedatacreatedate";
            rs.execute(sql);
            Set<String> deleteSet = new HashSet<>();
            ArrayList<List> insertLists = new ArrayList<>();
            while (rs.next()) {
                String exist = rs.getString("exist");
                ArrayList<String> list1 = new ArrayList<>();
                list1.add(rs.getString("yg"));
                list1.add(rs.getString("sfgb"));
                list1.add(rs.getString("gbly"));
                list1.add(rs.getString("qcccxjggsjzw"));
                list1.add(rs.getString("dyzysq"));
                list1.add(rs.getString("zymb"));
                list1.add(rs.getString("grqkbc"));
                list1.add(rs.getString("wdd"));
                list1.add(rs.getString("jrzxm"));
                list1.add(rs.getString("jrzcsd"));
                list1.add(rs.getString("ydyyd"));
                list1.add(rs.getString("djls"));

                list1.add(rs.getString("sfgbtxt"));
                list1.add(rs.getString("gblytxt"));
                list1.add(rs.getString("wddtxt"));
                list1.add(rs.getString("jrzxmtxt"));
                list1.add(rs.getString("jrzcsdtxt"));
                list1.add(rs.getString("ydyydtxt"));
                list1.add(rs.getString("djlstxt"));

                if (StringUtils.isNotEmpty(exist)) {
                    //更新
                    deleteSet.add(exist);
                }
                //新增
                insertLists.add(list1);

            }
            if (CollectionUtils.isNotEmpty(deleteSet)) {
                rs.execute("delete from uf_cadre where id in (" + String.join(",", deleteSet) + ")");
            }
            String insertSql = "insert into uf_cadre(yg,sfgb,gbly,qcccxjggsjzw,dyzysq,zymb,grqkbc,wdd,jrzxm,jrzcsd,ydyyd,djls" +
                    ",sfgbtxt,gblytxt,wddtxt,jrzxmtxt,jrzcsdtxt,ydyydtxt,djlstxt) " +
                    "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            rs.executeBatchSql(insertSql, insertLists);
        } catch (Exception e) {
            bb.writeLog("cadre-e" + e.getMessage());
        }
    }

    private void age() {
        try {
            RecordSet rs = new RecordSet();
            RecordSet rs2 = new RecordSet();

            String sql = "select  t1.id,DATEDIFF(DAY, \n" +
                    "        CASE WHEN ISDATE(SUBSTRING(t1.certificatenum, 7, 8)) = 1 \n" +
                    "            THEN SUBSTRING(t1.certificatenum, 7, 8) \n" +
                    "            ELSE t1.birthday END, \n" +
                    "        GETDATE()\n" +
                    "    ) / 365 AS age from hrmresource t1";
            String advanceSql = "select count(0) as exist from cus_fielddata where scopeid = 1 and scope='HrmCustomFieldByInfoType' and id=?";
            String insertSql = "insert into cus_fielddata (scopeid,scope,id) values (1,'HrmCustomFieldByInfoType',?)";
            rs.execute(sql);
            ArrayList<List> lists = new ArrayList<>();
            while (rs.next()) {
                ArrayList<String> list = new ArrayList<>();
                list.add(rs.getString("age"));
                list.add(rs.getString("id"));
                lists.add(list);
                rs2.executeQuery(advanceSql, rs.getString("id"));
                rs2.next();
                int exist = rs2.getInt("exist");
                if (exist == 0) {
                    rs2.executeUpdate(insertSql, rs.getString("id"));
                }
            }

            String updateSql = "update cus_fielddata set field65=? where scopeid = 1 and scope='HrmCustomFieldByInfoType' and id=?";
            rs.executeBatchSql(updateSql, lists);
        } catch (Exception e) {
            bb.writeLog("age-e-" + e.getMessage());
        }
    }

    private void rzlc() {
        try {
            RecordSet rs = new RecordSet();

            String sql = "select * from uf_roster_rzlc";
            HashSet<String> set = new HashSet<>();
            rs.execute(sql);
            while (rs.next()) {
                set.add(rs.getString("rzryid"));
            }
            sql = "select tt.*,t2.selectname as sfstwjrtxt,h.lastname as ntrytxt  from (    SELECT \n" +
                    "        ntry,sfstwjr,rzryid,yglydx,\n" +
                    "        ROW_NUMBER() OVER (\n" +
                    "            PARTITION BY rzryid \n" +
                    "            ORDER BY rzsj DESC\n" +
                    "        ) AS RowNum \n" +
                    "    FROM formtable_main_36\n" +
                    "\t) as tt \n" +
                    "\tleft join workflow_SelectItem t2 on t2.fieldid=14218 and t2.selectvalue=tt.sfstwjr\n" +
                    "\tleft join hrmresource h on h.id=tt.ntry\n" +
                    "\twhere RowNum=1";
            rs.execute(sql);
            ArrayList<List> insertLists = new ArrayList<>();
            ArrayList<List> updateLists = new ArrayList<>();
            while (rs.next()) {
                ArrayList<String> list = new ArrayList<>();
                list.add(rs.getString("ntry"));
                list.add(rs.getString("ntrytxt"));
                list.add(rs.getString("sfstwjr"));
                list.add(rs.getString("sfstwjrtxt"));
                list.add(rs.getString("yglydx"));
                list.add(rs.getString("rzryid"));
                if (set.contains(rs.getString("rzryid"))) {
                    updateLists.add(list);
                } else {
                    insertLists.add(list);
                }
            }

            String insertSql = "insert into uf_roster_rzlc (ntry,ntrytxt,sfstwjr,sfstwjrtxt,yglydx,rzryid)values(?,?,?,?,?,?)";
            String updateSql = "update uf_roster_rzlc set ntry=?,ntrytxt=?,sfstwjr=?,sfstwjrtxt=?,yglydx=? where rzryid=?";
            rs.executeBatchSql(insertSql, insertLists);
            rs.executeBatchSql(updateSql, updateLists);
        } catch (Exception e) {
            bb.writeLog("rzlc-e-" + e.getMessage());
        }
    }

    private void jjlxrxx() {
        try {
            RecordSet rs = new RecordSet();

            String sql = "select * from uf_roster_jjlxrxx";
            HashSet<String> set = new HashSet<>();
            rs.execute(sql);
            while (rs.next()) {
                set.add(rs.getString("xm"));
            }
            sql = "select tt.*,t2.selectname as gxTxt from (    SELECT \n" +
                    "        xm,dh, \n" +
                    "        gx, \n" +
                    "        lxrxm, \n" +
                    "        ROW_NUMBER() OVER (\n" +
                    "            PARTITION BY xm\n" +
                    "            ORDER BY id DESC\n" +
                    "        ) AS RowNum\n" +
                    "    FROM uf_shgx\n" +
                    "    WHERE sfjjlxr = 0\n" +
                    ") tt\n" +
                    "left join workflow_SelectItem t2 on t2.fieldid=18509 and t2.selectvalue=tt.gx\n" +
                    " where tt.RowNum = 1 ";
            rs.execute(sql);
            ArrayList<List> updateLists = new ArrayList<>();
            ArrayList<List> insertLists = new ArrayList<>();
            while (rs.next()) {
                ArrayList<String> list = new ArrayList<>();
                list.add(rs.getString("zlsl"));
                list.add(rs.getString("gxTxt"));
                list.add(rs.getString("lxrxm"));
                list.add(rs.getString("dh"));
                list.add(rs.getString("xm"));
                if (set.contains(rs.getString("xm"))) {
                    updateLists.add(list);
                } else {
                    insertLists.add(list);
                }
            }

            String insertSql = "insert into uf_roster_jjlxrxx (zlsl,gx,lxrxm,dh,xm)values(?,?,?,?,?)";
            String updateSql = "update uf_roster_jjlxrxx set zlsl=?,gx=?,lxrxm=?,dh=? where xm=?";
            rs.executeBatchSql(insertSql, insertLists);
            rs.executeBatchSql(updateSql, updateLists);
        } catch (Exception e) {
            bb.writeLog("jjlxrxx-e-" + e.getMessage());
        }
    }

    private void zlxx() {
        try {
            RecordSet rs = new RecordSet();

            String sql = "select * from uf_roster_zlxx";
            HashSet<String> set = new HashSet<>();
            rs.execute(sql);
            while (rs.next()) {
                set.add(rs.getString("xm"));
            }
            sql = " SELECT " +
                    "        xm, \n" +
                    "        COUNT(*) AS zlsl, \n" +
                    "        MAX(csrq) AS zlcsrq\n" +
                    "    FROM uf_shgx\n" +
                    "    WHERE gx = 1\n" +
                    "    GROUP BY xm";
            rs.execute(sql);
            ArrayList<List> updateLists = new ArrayList<>();
            ArrayList<List> insertLists = new ArrayList<>();
            while (rs.next()) {
                ArrayList<String> list = new ArrayList<>();
                list.add(rs.getString("zlsl"));
                list.add(rs.getString("zlcsrq"));
                list.add(rs.getString("xm"));
                if (set.contains(rs.getString("xm"))) {
                    updateLists.add(list);
                } else {
                    insertLists.add(list);
                }
            }

            String insertSql = "insert into uf_roster_zlxx (zlsl,zlcsrq,xm)values(?,?,?)";
            String updateSql = "update uf_roster_zlxx set zlsl=?,zlcsrq=? where xm=?";
            rs.executeBatchSql(insertSql, insertLists);
            rs.executeBatchSql(updateSql, updateLists);
        } catch (Exception e) {
            bb.writeLog("zlxx-e-" + e.getMessage());
        }
    }

    private void htxy() {
        try {
            RecordSet rs = new RecordSet();

            String sql = "select * from uf_roster_htxy";
            HashSet<String> set = new HashSet<>();
            rs.execute(sql);
            while (rs.next()) {
                set.add(rs.getString("ygxm"));
            }
            sql = "  SELECT SubQuery.*,t.ztmc as htgsmctxt\n" +
                    "FROM (\n" +
                    "    SELECT \n" +
                    "        ygxm, \n" +
                    "        htgsmc, \n" +
                    "        zt, \n" +
                    "        htqsrq, \n" +
                    "        htzzrq, \n" +
                    "        qdcs,\n" +
                    "        ROW_NUMBER() OVER (\n" +
                    "            PARTITION BY ygxm\n" +
                    "            ORDER BY htzzrq DESC, htqsrq DESC\n" +
                    "        ) AS RowNum\n" +
                    "    FROM uf_htxy \n" +
                    "    WHERE ygxm IS NOT NULL\n" +
                    ") AS SubQuery\n" +
                    "\n" +
                    "left join uf_htzt t on t.id= SubQuery.htgsmc\n" +
                    "WHERE RowNum = 1\n";
            rs.execute(sql);
            ArrayList<List> updateLists = new ArrayList<>();
            ArrayList<List> insertLists = new ArrayList<>();
            while (rs.next()) {
                ArrayList<String> list = new ArrayList<>();
                list.add(rs.getString("htgsmc"));
                list.add(rs.getString("htgsmctxt"));
                list.add(rs.getString("zt"));
                list.add(rs.getString("htqsrq"));
                list.add(rs.getString("htzzrq"));
                list.add(rs.getString("qdcs"));
                list.add(rs.getString("ygxm"));
                if (set.contains(rs.getString("ygxm"))) {
                    updateLists.add(list);
                } else {
                    insertLists.add(list);
                }
            }

            String insertSql = "insert into uf_roster_htxy (htgsmc,htgsmctxt,zt,htqsrq,htzzrq,qdcs,ygxm)values(?,?,?,?,?,?,?)";
            String updateSql = "update uf_roster_htxy set htgsmc=?,htgsmctxt=?,zt=?,htqsrq=?,htzzrq=?,qdcs=? where ygxm=?";
            rs.executeBatchSql(insertSql, insertLists);
            rs.executeBatchSql(updateSql, updateLists);
        } catch (Exception e) {
            bb.writeLog("htxy-e-" + e.getMessage());
        }
    }

//    private void khbhqxxrg(RecordSet rs) {
//        String sql = "select * from uf_roster_khbhqxxrg";
//        HashSet<String> set = new HashSet<>();
//        rs.execute(sql);
//        while (rs.next()) {
//            set.add(rs.getString("kid"));
//        }
//        sql = "   SELECT id, ygxm,\n" +
//                "ROW_NUMBER() OVER (PARTITION BY ygxm ORDER BY id DESC) AS rn\n" +
//                "    FROM uf_khbhqxxrg \n" +
//                "    WHERE lx = 0";
//        rs.execute(sql);
//        ArrayList<List> insertLists = new ArrayList<>();
//        while (rs.next()) {
//            ArrayList<String> list = new ArrayList<>();
//            list.add(rs.getString("id"));
//            list.add(rs.getString("ygxm"));
//            list.add(rs.getString("rn"));
//            if (!set.contains(rs.getString("id"))) {
//                insertLists.add(list);
//            }
//        }
//
//        String insertSql = "insert into uf_roster_khbhqxxrg (kid,ygxm,rr)values(?,?,?)";
//        rs.executeBatchSql(insertSql, insertLists);
//    }
}
